package work.dao;

        import org.apache.ibatis.annotations.Mapper;
        import org.springframework.stereotype.Repository;
        import work.po.Book;

        import java.sql.*;
        import java.util.ArrayList;
        import java.util.List;

@Repository
//@Mapper

//    配置mybatis有bug，暂时不配置mybatis先，先做好数据测试
//    可以设置不同的检索模式，查作者，查作品等等，返回不同的值
//    TODO 支持单个模块模糊搜索，多个关键字查询
// 搜索书名 并加上作家的要求 (优先级)
// 多重搜索 应该是指加上检索要求

public class BookDao {
    public String JDBC_URL = "jdbc:mysql://localhost:3306/test_wenxue?serverTimezone=GMT&useSSL=true&useUnicode=true&characterEncoding=UTF-8";
    public String JDBC_USER = "root";
    public String JDBC_PASSWORD = "123456789";

    public List<Book> search(String[] searchList) throws SQLException {
        List<Book> books = new ArrayList<>();
        String bookNameList = "";
        for (String str : searchList) {
            bookNameList = bookNameList + str + "|";
        }
        bookNameList = bookNameList.substring(0,bookNameList.length()-1);
        System.out.println(bookNameList);
        String sqlStr = "SELECT * FROM table1 WHERE bookName REGEXP " + "'" + bookNameList + "'" +
                        "OR authorName REGEXP " + "'" + bookNameList + "'" +
                        "OR liupai REGEXP " + "'" + bookNameList + "'" +
                        "OR chengshi REGEXP " + "'" + bookNameList + "'" +
                        "OR location REGEXP " + "'" + bookNameList + "'";
        // 获取连接:
        try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
            try (Statement stmt = conn.createStatement()) {
                try (ResultSet rs = stmt.executeQuery(sqlStr)) {
                    while (rs.next()) {
                        Book book = new Book();
                        book.setName(rs.getString(1));
                        book.setAuthor(rs.getString(2));
                        book.setTime(rs.getString(3));
                        book.setLocation(rs.getString(4));
                        book.setJingdu(rs.getDouble(5));
                        book.setWeidu(rs.getDouble(6));

                        books.add(book);
                    }
                }
            }
        }
        return books;
    }

    public List<Book> searchByBookName(String[] searchList) throws SQLException {
        List<Book> books = new ArrayList<>();
        String bookNameList = "";
        for (String str : searchList) {
            bookNameList = bookNameList + str + "|";
        }
        bookNameList = bookNameList.substring(0,bookNameList.length()-1);
        String sqlStr = "SELECT * FROM table1 WHERE bookName REGEXP " + "'" + bookNameList + "'";
        try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
            try (Statement stmt = conn.createStatement()) {
                try (ResultSet rs = stmt.executeQuery(sqlStr)) {
                    while (rs.next()) {
                        Book book = new Book();
                        book.setName(rs.getString(1));
                        book.setAuthor(rs.getString(2));
                        book.setTime(rs.getString(3));
                        book.setLocation(rs.getString(4));
                        book.setJingdu(rs.getDouble(5));
                        book.setWeidu(rs.getDouble(6));

                        books.add(book);
                    }
                }
            }
        }

        return books;
    }

    public List<Book> searchByAuthorName(String[] searchList) throws SQLException {
        List<Book> books = new ArrayList<>();
        String bookNameList = "";
        for (String str : searchList) {
            bookNameList = bookNameList + str + "|";
        }
        bookNameList = bookNameList.substring(0,bookNameList.length()-1);
        String sqlStr = "SELECT * FROM table1 WHERE authorName REGEXP " + "'" + bookNameList + "'";
        try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
            try (Statement stmt = conn.createStatement()) {
                try (ResultSet rs = stmt.executeQuery(sqlStr)) {
                    while (rs.next()) {
                        Book book = new Book();
                        book.setName(rs.getString(1));
                        book.setAuthor(rs.getString(2));
                        book.setTime(rs.getString(3));
                        book.setLocation(rs.getString(4));
                        book.setJingdu(rs.getDouble(5));
                        book.setWeidu(rs.getDouble(6));

                        books.add(book);
                    }
                }
            }
        }

        return books;
    }

    public List<Book> searchByTime(String[] searchList) throws SQLException {
        List<Book> books = new ArrayList<>();
        String bookNameList = "";
        for (String str : searchList) {
            bookNameList = bookNameList + str + "|";
        }
        bookNameList = bookNameList.substring(0,bookNameList.length()-1);
        String sqlStr = "SELECT * FROM table1 WHERE time REGEXP " + "'" + bookNameList + "'";
        try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
            try (Statement stmt = conn.createStatement()) {
                try (ResultSet rs = stmt.executeQuery(sqlStr)) {
                    while (rs.next()) {
                        Book book = new Book();
                        book.setName(rs.getString(1));
                        book.setAuthor(rs.getString(2));
                        book.setTime(rs.getString(3));
                        book.setLocation(rs.getString(4));
                        book.setJingdu(rs.getDouble(5));
                        book.setWeidu(rs.getDouble(6));

                        books.add(book);
                    }
                }
            }
        }

        return books;
    }

    public List<Book> searchByLiupai(String[] searchList) throws SQLException {
        List<Book> books = new ArrayList<>();
        String bookNameList = "";
        for (String str : searchList) {
            bookNameList = bookNameList + str + "|";
        }
        bookNameList = bookNameList.substring(0,bookNameList.length()-1);
        String sqlStr = "SELECT * FROM table1 WHERE liupai REGEXP " + "'" + bookNameList + "'";
        try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
            try (Statement stmt = conn.createStatement()) {
                try (ResultSet rs = stmt.executeQuery(sqlStr)) {
                    while (rs.next()) {
                        Book book = new Book();
                        book.setName(rs.getString(1));
                        book.setAuthor(rs.getString(2));
                        book.setTime(rs.getString(3));
                        book.setLocation(rs.getString(4));
                        book.setJingdu(rs.getDouble(5));
                        book.setWeidu(rs.getDouble(6));

                        books.add(book);
                    }
                }
            }
        }

        return books;
    }
}
